package SiTuQingYou.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import SiTuQingYou.util.DBHelper;
import SiTuQingYou.util.Pager;
import SiTuQingYou.vo.City;
import SiTuQingYou.vo.Guider;

public class GuiderDao 
{
	private  GuiderDao() {}
	private static  GuiderDao gd= new  GuiderDao();
	public static  GuiderDao getgd()
	{
		return gd;
	}
	
	 public ArrayList<Guider> allguider(String name){
		 	Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				conn = DBHelper.getDB().getConn();
				String sql = "select * from guider where fakedelete=0   and  guidername like '%"+name+"%'";
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				ArrayList<Guider> gui = new ArrayList<Guider>();
				while(rs.next()) {
					Guider guider = new Guider();
					guider.setId(rs.getInt("id"));
					guider.setGuidername(rs.getString("guidername"));//导游名字
					guider.setGuidersex(rs.getBoolean("guidersex"));//性别
					guider.setGuideridnumber(rs.getString("guideridnumber"));//身份证号
					guider.setGuiderphone(rs.getString("guiderphone"));//手机号
					guider.setGuiderpirce(rs.getFloat("guiderprice"));//导游费
					guider.setGuiderphoto(rs.getString("guiderphoto"));//头像
					guider.setGuiderinfo(rs.getString("guiderinfo"));//导游信息
					gui.add(guider);
				}
				return gui;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DBHelper.getDB().close(rs, ps, conn);
			}
			return null;
	 }
	
	 public ArrayList<Guider> allguider(String name,int guidercityid){
		 	Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				conn = DBHelper.getDB().getConn();
				String sql = "select * from guider,city where fakedelete=0   and  guidercityid=city.id and guidername like '%"+name+"%'";
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				ArrayList<Guider> gui = new ArrayList<Guider>();
				while(rs.next()) {
					Guider guider = new Guider();
					guider.setId(rs.getInt("id"));
					guider.setGuidername(rs.getString("guidername"));//导游名字
					guider.setGuidersex(rs.getBoolean("guidersex"));//性别
					guider.setGuideridnumber(rs.getString("guideridnumber"));//身份证号
					guider.setGuiderphone(rs.getString("guiderphone"));//手机号
					guider.setGuiderpirce(rs.getFloat("guiderprice"));//导游费
					guider.setGuiderphoto(rs.getString("guiderphoto"));//头像
					guider.setGuiderinfo(rs.getString("guiderinfo"));//导游信息
					gui.add(guider);
				}
				return gui;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DBHelper.getDB().close(rs, ps, conn);
			}
			return null;
	 }
	
	 
	 
	
	//从数据库中读取前六个导游
	public ArrayList<Guider> getSixGuider()
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null; 
		try {
			conn=DBHelper.getDB().getConn();
			String sql="select * from guider where fakedelete=0 limit 0,6 ";
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			ArrayList<Guider> guiders=new ArrayList<Guider>();
			while(rs.next())
			{
				Guider guider=new Guider();
				guider.setGuiderinfo(rs.getString("guiderinfo"));
			   	guider.setGuiderrealname(rs.getString("guiderrealname"));
			   	guider.setGuiderphoto(rs.getString("guiderphoto"));
			   	guiders.add(guider);
			}
			return guiders;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	    finally
	    {
	    	DBHelper.getDB().close(rs, ps, conn);
	    }
		return null;
	}
	
	
	//添加导游到数据库
	public void addOneGuider(Guider guider)
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null; 
		
		
		try 
		{
			conn = DBHelper.getDB().getConn();
			String sql = "insert into guider(guidername,guidersex,guideridnumber,guiderphone,guiderprice,guiderinfo,guiderphoto,guidercityid)"
					+ "value(?,?,?,?,?,?,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1,guider.getGuidername());
			ps.setBoolean(2,guider.isGuidersex());
			ps.setString(3,guider.getGuideridnumber());
			ps.setString(4,guider.getGuiderphone());
			ps.setFloat(5,guider.getGuiderprice());
			ps.setString(6,guider.getGuiderinfo());
			ps.setString(7,guider.getGuiderphoto());
			ps.setInt(8, guider.getCity().getId());
			ps.executeUpdate();
		} 
		catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 finally
		 {
		    	DBHelper.getDB().close(rs, ps, conn);
		 }
		
	}
	
	//查询出所有导游
	public ArrayList<Guider> showAllGuiders(Pager pager)
	{
		pager.setDataCount(this.getGuiderCount());
		
		ArrayList<Guider> allGuider = new ArrayList<Guider>();
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null; 
		
		
		try 
		{
			conn = DBHelper.getDB().getConn();
			String sql = "select * from guider,city where fakedelete=0 and guidercityid=city.id  ORDER BY guider.id limit ?,?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, pager.getFirstRow());//获得limit后面第一个数是几
			ps.setInt(2, pager.getPageSize());//获得每页几条记录
			rs = ps.executeQuery();
			
			while(rs.next()) {
				Guider guider = new Guider();
				guider.setId(rs.getInt("id"));
				City city = new City();
				city.setId(rs.getInt("city.id"));
				city.setCityname(rs.getString("cityname"));
				guider.setCity(city);
				guider.setGuidername(rs.getString("guidername"));//导游名字
				guider.setGuidersex(rs.getBoolean("guidersex"));//性别
				guider.setGuideridnumber(rs.getString("guideridnumber"));//身份证号
				guider.setGuiderphone(rs.getString("guiderphone"));//手机号
				guider.setGuiderpirce(rs.getFloat("guiderprice"));//导游费
				guider.setGuiderphoto(rs.getString("guiderphoto"));//头像
				guider.setGuiderinfo(rs.getString("guiderinfo"));//导游信息
				
				allGuider.add(guider);
			}
			
		} 
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
		    	DBHelper.getDB().close(rs, ps, conn);
		}
		return allGuider;
	}
	
	//获得导游总数
	public int getGuiderCount()
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null; 
		
		int count =0;
		try {
			conn = DBHelper.getDB().getConn();
			String sql="select count(id) as guidercount from guider where fakedelete=0";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs.next()) {
				count = rs.getInt("guidercount");
			}
			
		} 
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
		    	DBHelper.getDB().close(rs, ps, conn);
		}
		return count;
	}
	
	//根据导游id查询出此导游详细信息
	public Guider loofForDetailedInfoById(int guiderid)
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null; 
		Guider guider = new Guider();
		try 
		{
			conn = DBHelper.getDB().getConn();
			String sql = "select * from guider,city where guidercityid=city.id and guider.id=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1,guiderid);
			rs = ps.executeQuery();
			if(rs.next()) {
				
				guider.setId(rs.getInt("id"));
				guider.setGuidername(rs.getString("guidername"));//导游名字
				guider.setGuiderinfo(rs.getString("guiderinfo"));//导游信息
				guider.setGuiderphone(rs.getString("guiderphone"));//导游手机号
				guider.setGuiderphoto(rs.getString("guiderphoto"));//导游头像
				guider.setGuiderpirce(rs.getFloat("guiderprice"));//导游价格
				City city=new City();
				city.setId(rs.getInt("guidercityid"));
				city.setCityname(rs.getString("cityname"));
				guider.setCity(city);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
		    	DBHelper.getDB().close(rs, ps, conn);
		}
		return guider;
	}
	
	
	//修改导游信息
	public void toReviseGuiderInfo(Guider g)
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null; 
		
		
		try 
		{
			conn = DBHelper.getDB().getConn();
			String sql;
			if(g.getGuiderphoto()!=null&&g.getGuiderphoto()!="") {//如果修改过了图片
				sql = "update guider set guiderphone=?,guiderprice=?,guiderinfo=?,guiderphoto=?,guidercityid=?  where id=?";
				ps = conn.prepareStatement(sql);
				ps.setString(1,g.getGuiderphone());
				ps.setFloat(2,g.getGuiderprice());
				ps.setString(3,g.getGuiderinfo());
				ps.setString(4,g.getGuiderphoto());
				ps.setInt(5, g.getCity().getId());
				ps.setInt(6,g.getId());
				ps.executeUpdate();
			}
			else//如果没有修改图片
			{
				sql = "update guider set guiderphone=?,guiderprice=?,guiderinfo=? where id=?";
				ps = conn.prepareStatement(sql);
				
				ps.setString(1,g.getGuiderphone());
				ps.setFloat(2,g.getGuiderprice());
				ps.setString(3,g.getGuiderinfo());
				ps.setInt(4,g.getId());
				ps.executeUpdate();
			}
			
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
		    	DBHelper.getDB().close(rs, ps, conn);
		}
		
	}
	
	
	//根据导游id，删除（假删除）此导游
	public void deleteGuiderById(int theid)
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
	    
	    try 
	    {
	    	conn = DBHelper.getDB().getConn();
		    String sql = "update guider set fakedelete=1 where id=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1,theid);
			ps.executeUpdate();
		} 
	    catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	    finally
		{
		    	DBHelper.getDB().close(rs, ps, conn);
		}
	}
	
	
}
